package net.kesumu.yushabuserver.daoimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import net.kesumu.yushabuserver.dao.IUserDao;
import net.kesumu.yushabuserver.entity.User;
import net.kesumu.yushabuserver.utils.DataSource;

public class UserDaoImpl implements IUserDao{

    @Override
    public boolean register(User user) {
        String email = user.getEmail();
        String password = user.getPassword();
        
        DataSource ds = new DataSource();
        String preSelectSQL = "SELECT * FROM " + DataSource.dbName + ".user "
                                + "WHERE email='" + email + "'";
        // System.out.println(sql);
        Connection conn = ds.getConnection();
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(preSelectSQL).executeQuery();
            if(resultSet.next()) {
                return false;
            }
            String insertSQL = "INSERT INTO " + DataSource.dbName + ".user"
                                + "(email, password)"
                                + "VALUES"
                                + "('"+email+"', '"+password+"')";
            conn.prepareStatement(insertSQL).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ds.close();
        }
        return true;
    }

    @Override
    public User login(String email, String password) {
        User user = new User();
        
        DataSource ds = new DataSource();
        String sql = "";
        sql = "SELECT * FROM " + DataSource.dbName + ".user "
                + "WHERE email='" + email + "'"
                + " AND password='" + password + "'";
        // System.out.println(sql);
        Connection conn = ds.getConnection();
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(sql).executeQuery();
            if (resultSet.next()) {
                user.setUserId(Integer.parseInt(resultSet.getString(1)));
                user.setEmail(resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return user;
    }

    @Override
    public User getUserById(int id) {
        User user = new User();
        
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String sql = "";
        sql = "SELECT * FROM " + DataSource.dbName + ".userinfo "
                + "WHERE userId='" + id + "'";
        // System.out.println(sql);
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(sql).executeQuery();
            if (resultSet.next()) {
                user.setName(resultSet.getString(1));
                user.setGender(resultSet.getString(2));
                user.setArea(resultSet.getString(3));
                user.setUserId(Integer.parseInt(resultSet.getString(4)));
                user.setSignature(resultSet.getString(5));
                user.setYuyuChatId(resultSet.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return user;
    }
    
    @Override
    public User getUserByYuYuChatId(String yuyuChatId) {
        User user = new User();
        
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String sql = "";
        sql = "SELECT * FROM " + DataSource.dbName + ".userinfo "
                + "WHERE yuyuChatId='" + yuyuChatId + "'";
        // System.out.println(sql);
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(sql).executeQuery();
            if (resultSet.next()) {
                user.setName(resultSet.getString(1));
                user.setGender(resultSet.getString(2));
                user.setArea(resultSet.getString(3));
                user.setUserId(Integer.parseInt(resultSet.getString(4)));
                user.setSignature(resultSet.getString(5));
                user.setYuyuChatId(resultSet.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return user;
    }

    @Override
    public boolean updateUser(User user) {
        User testUserExist = getUserById(user.getUserId());
        boolean exist = false;
        if(testUserExist.getUserId() != 0) {
            exist = true;
        }
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        if(exist) {
            try {
                String updateSQL = "UPDATE " + DataSource.dbName + ".userinfo ui "
                                    + "SET ui.name = '"+user.getName()
                                    +"', ui.gender ='"+user.getGender()
                                    +"', ui.area = '"+user.getArea()
                                    +"', ui.signature = '"+user.getSignature()
                                    +"', ui.yuyuChatId = '"+user.getYuyuChatId()+"' "
                                    + "WHERE ui.userId = '"+user.getUserId()+"'";    
//                System.out.println(updateSQL);
                conn.prepareStatement(updateSQL).executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }finally {
                ds.close();
            }
        }else {
            try {
                String insertSQL = "INSERT INTO " + DataSource.dbName + ".userinfo"
                                    + "(name, gender, area, userId,signature,yuyuChatId)"
                                    + "VALUES"
                                    + "('"+user.getName()+"', '"+user.getGender()+"', '"+user.getArea()+"','"+user.getUserId()+"','"+user.getSignature()+"','"+user.getYuyuChatId()+"')";
                conn.prepareStatement(insertSQL).executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                return false;
            }finally {
                ds.close();
            }
        }
        return true;
    }

    @Override
    public ArrayList<User> getUsersByName(String name) {
        ArrayList<User> users = new ArrayList<User>();
        DataSource ds = new DataSource();
        String sql = "";
        sql = "SELECT * FROM " + DataSource.dbName + ".userinfo "
                + "WHERE name like '%" + name + "%'"
                + " OR name like '" + name + "%'" 
                + " OR name like '%"+ name +"'";
        // System.out.println(sql);
        Connection conn = ds.getConnection();
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(sql).executeQuery();
            while (resultSet.next()) {
//                System.out.println(resultSet.getString(1));
                User user = new User();
                user.setName(resultSet.getString(1));
                user.setGender(resultSet.getString(2));
                user.setArea(resultSet.getString(3));
                user.setUserId(resultSet.getInt(4));
                user.setSignature(resultSet.getString(5));
                user.setYuyuChatId(resultSet.getString(6));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return users;
    }


    
}
